﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using System.Linq.Expressions;

namespace Datalayer
{
    internal abstract class DbExecQueryBase : IQueryMgr
    {
        private bool _disposed;
        private LuanVanDBDataContext db;

        protected DbExecQueryBase(LuanVanDBDataContext db)
        {
            this.db = db;
        }
        //
        #region BaoTroXaHoi

        public List<BaoTroXaHoi> BTXH_GetAllBaoTroXaHoi()
        {
            var q = from p in db.BaoTroXaHois
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<BaoTroXaHoi>();
        }

        public BaoTroXaHoi BTXH_GetBaoTroXaHoiByID(byte BtxhID)
        {

            var q = from p in db.BaoTroXaHois
                    where p.Ma_BaoTroXH == BtxhID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region CapHoc
        public List<CapHoc> CH_GetAllCapHoc()
        {
            var q = from p in db.CapHocs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<CapHoc>();
        }

        public CapHoc CH_GetCapHocByID(int ChID)
        {

            var q = from p in db.CapHocs
                    where p.Ma_CapHoc == ChID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region CheDoDieuDuong

        public List<CheDoDieuDuong> CDDD_GetAllCheDoDieuDuong()
        {
            var q = from p in db.CheDoDieuDuongs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<CheDoDieuDuong>();
        }

        public CheDoDieuDuong CDDD_GetCheDoDieuDuongByID(int CdddID)
        {

            var q = from p in db.CheDoDieuDuongs
                    where p.Ma_CheDoDieuDuong == CdddID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region ChinhSach

        public List<ChinhSach> CS_GetAllChinhSach()
        {
            var q = from p in db.ChinhSaches
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChinhSach>();
        }

        public ChinhSach CDDD_GetChinhSachByID(int CsID)
        {

            var q = from p in db.ChinhSaches
                    where p.Ma_ChinhSach == CsID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region ChiTiet_ChiTieuHGD

        public List<ChiTiet_ChiTieuHGD> CTCTHGD_GetAllChiTiet_ChiTieuHGD()
        {
            var q = from p in db.ChiTiet_ChiTieuHGDs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_ChiTieuHGD>();
        }
        public List<ChiTiet_ChiTieuHGD> CTCTHGD_GetChiTiet_ChiTieuHGDByIDHoNgheo(string IDHoNGheo)
        {
            var q = from p in db.ChiTiet_ChiTieuHGDs
                    where p.IsDeleted == false && p.Ma_HoNgheo ==IDHoNGheo
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_ChiTieuHGD>();
        }
        public ChiTiet_ChiTieuHGD CTCT_GetCTCTByID(int pCtct)
        {

            var q = from p in db.ChiTiet_ChiTieuHGDs
                    where p.Ma_ChiTietChiTieu == pCtct && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public ChiTiet_ChiTieuHGD CTCT_GetCTCTByIDandIDHN(int pCtct,string IDHoNgheo)
        {

            var q = from p in db.ChiTiet_ChiTieuHGDs
                    where p.Ma_ChiTieu == pCtct &&p.Ma_HoNgheo==IDHoNgheo && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Ctct_NewChiTiet_ChiTieuHGD(ChiTiet_ChiTieuHGD pCtct)
        {
            db.ChiTiet_ChiTieuHGDs.InsertOnSubmit(pCtct);
            db.SubmitChanges();
        }

        public void Ctct_EditChiTiet_ChiTieuHGD(ChiTiet_ChiTieuHGD pCtct)
        {
            var q = from p in db.ChiTiet_ChiTieuHGDs
                    where p.Ma_ChiTieu == pCtct.Ma_ChiTieu && p.Ma_HoNgheo==pCtct.Ma_HoNgheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_ChiTieuHGD obj = q.FirstOrDefault();
                obj.Ma_ChiTieu = pCtct.Ma_ChiTieu;
                obj.Ma_HoNgheo = pCtct.Ma_HoNgheo;
                obj.Tien = pCtct.Tien;
                obj.IsDeleted = pCtct.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Ctct_DelOrResChiTiet_ChiTieuHGD(int pCtctID)
        {

            var q = from p in db.ChiTiet_ChiTieuHGDs
                    where p.Ma_ChiTietChiTieu == pCtctID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_ChiTieuHGD obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region ChiTiet_DieuDuong

        public List<ChiTiet_DieuDuong> CTDD_GetAllChiTiet_DieuDuong()
        {
            var q = from p in db.ChiTiet_DieuDuongs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_DieuDuong>();
        }

        public ChiTiet_DieuDuong CTDD_GetChiTiet_DieuDuongByID(int pCtdd)
        {

            var q = from p in db.ChiTiet_DieuDuongs
                    where p.Ma_ChiTietDieuDuong == pCtdd && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        
        public void Ctdd_NewChiTiet_DieuDuong(ChiTiet_DieuDuong pCtdd)
        {
            db.ChiTiet_DieuDuongs.InsertOnSubmit(pCtdd);
            db.SubmitChanges();
        }

        public void Ctct_EditChiTiet_DieuDuong(ChiTiet_DieuDuong pCtdd)
        {
            var q = from p in db.ChiTiet_DieuDuongs
                    where p.Ma_ChiTietDieuDuong == pCtdd.Ma_ChiTietDieuDuong &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_DieuDuong obj = q.FirstOrDefault();
                obj.Ma_DieuDuong = pCtdd.Ma_DieuDuong;
                obj.Ma_NguoiCoCong = pCtdd.Ma_NguoiCoCong;
                obj.GhiChu = pCtdd.GhiChu;
                obj.IsDeleted = pCtdd.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Ctdd_DelOrResChiTiet_DieuDuong(int pCtddID)
        {

            var q = from p in db.ChiTiet_DieuDuongs
                    where p.Ma_ChiTietDieuDuong == pCtddID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_DieuDuong obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region ChiTiet_GiaiPhapHoTro

        public List<ChiTiet_GiaiPhapHoTro> CTGPHT_GetAllChiTiet_GiaiPhapHoTro()
        {
            var q = from p in db.ChiTiet_GiaiPhapHoTros
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_GiaiPhapHoTro>();
        }

        public ChiTiet_GiaiPhapHoTro CTGPHT_GetChiTiet_GiaiPhapHoTroByID(int pCtgpht)
        {

            var q = from p in db.ChiTiet_GiaiPhapHoTros
                    where p.MaChiTiet_GiaiPhap == pCtgpht && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public ChiTiet_GiaiPhapHoTro CTGPHT_GetChiTiet_GiaiPhapHoTroByIDGpandIDHongheo(int idGiaiPhap, string IDHongheo)
        {

            var q = from p in db.ChiTiet_GiaiPhapHoTros
                    where p.Ma_GiaiPhap == idGiaiPhap && p.Ma_HoNgheo ==IDHongheo && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Ctgpht_NewChiTiet_GiaiPhapHoTro(ChiTiet_GiaiPhapHoTro pCtgpht)
        {
            db.ChiTiet_GiaiPhapHoTros.InsertOnSubmit(pCtgpht);
            db.SubmitChanges();
        }

        public void Ctgpht_EditChiTiet_GiaiPhapHoTro(ChiTiet_GiaiPhapHoTro pCtgpht)
        {
            var q = from p in db.ChiTiet_GiaiPhapHoTros
                    where p.Ma_HoNgheo == pCtgpht.Ma_HoNgheo && p.Ma_GiaiPhap == pCtgpht.Ma_GiaiPhap &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_GiaiPhapHoTro obj = q.FirstOrDefault();
                obj.Ma_GiaiPhap = pCtgpht.Ma_GiaiPhap;
                obj.Ma_HoNgheo = pCtgpht.Ma_HoNgheo;
                obj.SoTien = pCtgpht.SoTien;
                obj.GhiChu = pCtgpht.GhiChu;
                obj.IsDeleted = pCtgpht.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Ctgpht_DelOrResChiTiet_GiaiPhapHoTro(int pCtgpht)
        {

            var q = from p in db.ChiTiet_GiaiPhapHoTros
                    where p.MaChiTiet_GiaiPhap == pCtgpht &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_GiaiPhapHoTro obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region ChiTiet_NguyenNhanThuNhap

        public List<ChiTiet_NguyenNhanThuNhap> CTNNTN_GetAllChiTiet_NguyenNhanThuNhap()
        {
            var q = from p in db.ChiTiet_NguyenNhanThuNhaps
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_NguyenNhanThuNhap>();
        }

        public List<ChiTiet_NguyenNhanThuNhap> CTNNTN_GetChiTiet_NguyenNhanThuNhapByIDHoNgheo(string IDHoNgheo)
        {
            var q = from p in db.ChiTiet_NguyenNhanThuNhaps
                    where p.IsDeleted == false && p.Ma_HoNgheo == IDHoNgheo
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_NguyenNhanThuNhap>();
        }

        public ChiTiet_NguyenNhanThuNhap CTNNTN_GetChiTiet_NguyenNhanThuNhapByID(int pCtnntn)
        {

            var q = from p in db.ChiTiet_NguyenNhanThuNhaps
                    where p.MaChiTiet == pCtnntn && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public ChiTiet_NguyenNhanThuNhap CTNNTN_GetChiTiet_NguyenNhanThuNhapByIDAndIDHN(int pCtnntn,string IDHoNgheo)
        {

            var q = from p in db.ChiTiet_NguyenNhanThuNhaps
                    where p.MaChiTiet == pCtnntn && p.Ma_HoNgheo==IDHoNgheo && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        
        public void Ctnntn_NewChiTiet_NguyenNhanThuNhap(ChiTiet_NguyenNhanThuNhap pCtnntn)
        {
            db.ChiTiet_NguyenNhanThuNhaps.InsertOnSubmit(pCtnntn);
            db.SubmitChanges();
        }

        public void Ctnntn_EditChiTiet_NguyenNhanThuNhap(ChiTiet_NguyenNhanThuNhap pCtnntn)
        {
            var q = from p in db.ChiTiet_NguyenNhanThuNhaps
                    where p.MaChiTiet == pCtnntn.MaChiTiet &&
                    p.Ma_HoNgheo==pCtnntn.Ma_HoNgheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_NguyenNhanThuNhap obj = q.FirstOrDefault();
                obj.Ma_HoNgheo = pCtnntn.Ma_HoNgheo;
                obj.Ma_NguyenNhan = pCtnntn.Ma_NguyenNhan;
                obj.IsDeleted = pCtnntn.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Ctnntn_DelOrResChiTiet_NguyenNhanThuNhap(int pCtnntn)
        {

            var q = from p in db.ChiTiet_NguyenNhanThuNhaps
                    where p.MaChiTiet == pCtnntn &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_NguyenNhanThuNhap obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }

        public void Ctnntn_Delete_ChiTiet_NguyenNhanThuNhapByHoNgheo(string IDHongheo)
        {

            var q = from p in db.ChiTiet_NguyenNhanThuNhaps
                    where p.Ma_HoNgheo == IDHongheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                foreach (var detail in q)
                {
                    db.ChiTiet_NguyenNhanThuNhaps.DeleteOnSubmit(detail);
                }
                db.SubmitChanges();
            }
        }
        #endregion

        #region ChiTiet_NguyenVongKhacPhuc

        public List<ChiTiet_NguyenVongKhacPhuc> CTNVKP_GetAllChiTiet_NguyenVongKhacPhuc()
        {
            var q = from p in db.ChiTiet_NguyenVongKhacPhucs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_NguyenVongKhacPhuc>();
        }

        public List<ChiTiet_NguyenVongKhacPhuc> CTNVKP_GetChiTiet_NguyenVongKhacPhucByIDHoNgheo(string IDHoNgheo)
        {
            var q = from p in db.ChiTiet_NguyenVongKhacPhucs
                    where p.IsDeleted == false && p.Ma_HoNgheo == IDHoNgheo
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_NguyenVongKhacPhuc>();
        }

        public ChiTiet_NguyenVongKhacPhuc CTNVKP_GetChiTiet_NguyenVongKhacPhucByID(int pCtnvkp)
        {

            var q = from p in db.ChiTiet_NguyenVongKhacPhucs
                    where p.Ma_ChiTiet == pCtnvkp && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public ChiTiet_NguyenVongKhacPhuc CTNVKP_GetChiTiet_NguyenVongKhacPhucByIDAndIDHN(int pCtnvkp,string IDHoNgheo)
        {

            var q = from p in db.ChiTiet_NguyenVongKhacPhucs
                    where p.Ma_ChiTiet == pCtnvkp && p.Ma_HoNgheo==IDHoNgheo && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Ctnvkp_NewChiTiet_NguyenVongKhacPhuc(ChiTiet_NguyenVongKhacPhuc pCtnvkp)
        {
            db.ChiTiet_NguyenVongKhacPhucs.InsertOnSubmit(pCtnvkp);
            db.SubmitChanges();
        }

        public void Ctnvkp_EditChiTiet_NguyenVongKhacPhuc(ChiTiet_NguyenVongKhacPhuc pCtnvkp)
        {
            var q = from p in db.ChiTiet_NguyenVongKhacPhucs
                    where p.Ma_ChiTiet == pCtnvkp.Ma_ChiTiet &&
                    p.Ma_HoNgheo==pCtnvkp.Ma_HoNgheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_NguyenVongKhacPhuc obj = q.FirstOrDefault();
                obj.Ma_HoNgheo = pCtnvkp.Ma_HoNgheo;
                obj.Ma_NguyenVong = pCtnvkp.Ma_NguyenVong;
                obj.IsDeleted = pCtnvkp.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Ctnvkp_DelOrResChiTiet_NguyenVongKhacPhuc(int pCtnvkp)
        {

            var q = from p in db.ChiTiet_NguyenVongKhacPhucs
                    where p.Ma_ChiTiet == pCtnvkp &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_NguyenVongKhacPhuc obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }

        public void Ctnvkp_DeleteChiTiet_NguyenVongKhacPhucByHoNgheo(string IDHoNgheo)
        {

            var q = from p in db.ChiTiet_NguyenVongKhacPhucs
                    where p.Ma_HoNgheo == IDHoNgheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                foreach (var detail in q)
                {
                    db.ChiTiet_NguyenVongKhacPhucs.DeleteOnSubmit(detail);
                }
                db.SubmitChanges();
            }
        }
        #endregion

        #region ChiTiet_TaiSanHGD

        public List<ChiTiet_TaiSanHGD> CTTS_GetAllChiTiet_TaiSanHGD()
        {
            var q = from p in db.ChiTiet_TaiSanHGDs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_TaiSanHGD>();
        }

        public ChiTiet_TaiSanHGD CTTS_GetChiTiet_TaiSanHGDByID(int pCtts)
        {

            var q = from p in db.ChiTiet_TaiSanHGDs
                    where p.Ma_ChiTietTaiSan == pCtts && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public ChiTiet_TaiSanHGD CTTS_GetChiTiet_TaiSanHGDByIDAndIdHN(int pCtts, string IDHoNgheo)
        {

            var q = from p in db.ChiTiet_TaiSanHGDs
                    where p.Ma_TaiSan == pCtts && p.Ma_HoNgheo==IDHoNgheo && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Ctts_NewChiTiet_TaiSanHGD(ChiTiet_TaiSanHGD pCtts)
        {
            db.ChiTiet_TaiSanHGDs.InsertOnSubmit(pCtts);
            db.SubmitChanges();
        }

        public void Ctts_EditChiTiet_TaiSanHGD(ChiTiet_TaiSanHGD pCtts)
        {
            var q = from p in db.ChiTiet_TaiSanHGDs
                    where p.Ma_TaiSan == pCtts.Ma_TaiSan && p.Ma_HoNgheo==pCtts.Ma_HoNgheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_TaiSanHGD obj = q.FirstOrDefault();
                obj.Ma_HoNgheo = pCtts.Ma_HoNgheo;
                obj.Ma_TaiSan = pCtts.Ma_TaiSan;
                obj.Loai = pCtts.Loai;
                obj.DienTich_SoLuong = pCtts.DienTich_SoLuong;
                obj.SoHuu = pCtts.SoHuu;
                obj.GiaTri = pCtts.GiaTri;
                obj.IsDeleted = pCtts.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Ctts_DelOrResChiTiet_TaiSanHGD(int pCtts)
        {

            var q = from p in db.ChiTiet_TaiSanHGDs
                    where p.Ma_ChiTietTaiSan == pCtts &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_TaiSanHGD obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region ChiTiet_ThuNhapHoGD

        public List<ChiTiet_ThuNhapHoGD> CTTNHGD_GetAllChiTiet_ThuNhapHoGD()
        {
            var q = from p in db.ChiTiet_ThuNhapHoGDs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_ThuNhapHoGD>();
        }
        public List<ChiTiet_ThuNhapHoGD> CTTNHGD_GetChiTiet_ThuNhapHoGDByIDHoNgheo (string idhongheo)
        {
            var q = from p in db.ChiTiet_ThuNhapHoGDs
                    where p.IsDeleted == false && p.Ma_HoNgheo==idhongheo
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ChiTiet_ThuNhapHoGD>();
        }
        public ChiTiet_ThuNhapHoGD CTTNHGD_GetChiTiet_ThuNhapHoGDByID(int pCttnhgd)
        {

            var q = from p in db.ChiTiet_ThuNhapHoGDs
                    where p.Ma_ChiTietThuNhap == pCttnhgd && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public ChiTiet_ThuNhapHoGD CTTNHGD_GetChiTiet_ThuNhapHoGDByIDAndIDHN(int pCttnhgd,string IDHoNgheo)
        {

            var q = from p in db.ChiTiet_ThuNhapHoGDs
                    where p.Ma_LoaiHinhKinhTe == pCttnhgd && p.Ma_HoNgheo == IDHoNgheo && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Cttnhgd_NewChiTiet_ThuNhapHoGD(ChiTiet_ThuNhapHoGD pCttnhgd)
        {
            db.ChiTiet_ThuNhapHoGDs.InsertOnSubmit(pCttnhgd);
            db.SubmitChanges();
        }

        public void Cttnhgd_EditChiTiet_ThuNhapHoGD(ChiTiet_ThuNhapHoGD pCttnhgd)
        {
            var q = from p in db.ChiTiet_ThuNhapHoGDs
                    where p.Ma_LoaiHinhKinhTe == pCttnhgd.Ma_LoaiHinhKinhTe &&
                    p.Ma_HoNgheo == pCttnhgd.Ma_HoNgheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_ThuNhapHoGD obj = q.FirstOrDefault();
                obj.Ma_HoNgheo = pCttnhgd.Ma_HoNgheo;
                obj.Ma_LoaiHinhKinhTe = pCttnhgd.Ma_LoaiHinhKinhTe;
                obj.Thu = pCttnhgd.Thu;
                obj.Chi = pCttnhgd.Chi;
                obj.IsDeleted = pCttnhgd.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Cttnhgd_DelOrResChiTiet_ThuNhapHoGD(int pCttnhgd)
        {

            var q = from p in db.ChiTiet_ThuNhapHoGDs
                    where p.Ma_ChiTietThuNhap == pCttnhgd &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ChiTiet_ThuNhapHoGD obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region DanToc
        public List<DanToc> DT_GetAllDanToc()
        {
            var q = from p in db.DanTocs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<DanToc>();
        }

        public DanToc DT_GetDanTocByID(int DtID)
        {

            var q = from p in db.DanTocs
                    where p.Ma_DanToc == DtID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region DoiTuongCoCong

        public List<DoiTuongCoCong> DTCC_GetAllDoiTuongCoCong()
        {
            var q = from p in db.DoiTuongCoCongs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<DoiTuongCoCong>();
        }

        public List<DoiTuongCoCong> DTCC_GetDoiTuongCoCongByIDMdd(int IDMucDieuDuong)
        {
            var q = from p in db.DoiTuongCoCongs
                    where p.IsDeleted == false && p.Ma_MucDieuDuong == IDMucDieuDuong
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<DoiTuongCoCong>();
        }

        public DoiTuongCoCong DTCC_GetDoiTuongCoCongByID(int pDtcc)
        {

            var q = from p in db.DoiTuongCoCongs
                    where p.Ma_DoiTuongCoCong == pDtcc && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Dtcc_NewDoiTuongCoCong(DoiTuongCoCong pDtcc)
        {
            db.DoiTuongCoCongs.InsertOnSubmit(pDtcc);
            db.SubmitChanges();
        }

        public void Dtcc_EditDoiTuongCoCong(DoiTuongCoCong pDtcc)
        {
            var q = from p in db.DoiTuongCoCongs
                    where p.Ma_DoiTuongCoCong == pDtcc.Ma_DoiTuongCoCong &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                DoiTuongCoCong obj = q.FirstOrDefault();
                obj.Ten_DoiTuongCocong = pDtcc.Ten_DoiTuongCocong;
                obj.Ma_MucDieuDuong = pDtcc.Ma_MucDieuDuong;
                obj.GhiChu = pDtcc.GhiChu;
                obj.IsDeleted = pDtcc.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Dtcc_DelOrResDoiTuongCoCong(int pDtcc)
        {

            var q = from p in db.DoiTuongCoCongs
                    where p.Ma_DoiTuongCoCong == pDtcc &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                DoiTuongCoCong obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region GiaiPhap_KhacPhuc
        public List<GiaiPhap_KhacPhuc> GPKP_GetAllGiaiPhap_KhacPhuc()
        {
            var q = from p in db.GiaiPhap_KhacPhucs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<GiaiPhap_KhacPhuc>();
        }

        public GiaiPhap_KhacPhuc GPKP_GetGiaiPhap_KhacPhucByID(int GpID)
        {

            var q = from p in db.GiaiPhap_KhacPhucs
                    where p.Ma_GiaiPhap == GpID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region HoNgheo

        public List<HoNgheo> HN_GetAllHoNgheo()
        {
            var q = from p in db.HoNgheos
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<HoNgheo>();
        }
        public List<HoNgheo> HN_GetHoNgheoByPhuongID(int PhuongID)
        {
            var q = from p in db.HoNgheos
                    where p.IsDeleted == false && p.Ma_Phuong == PhuongID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<HoNgheo>();
        }
        public List<HoNgheo> HN_GetHoNgheoByQuanID(int QuanID)
        {
            var q = from p in db.HoNgheos
                    join j in db.PhuongXas on p.Ma_Phuong equals j.Ma_Phuong
                    where p.IsDeleted == false && j.Ma_QuanHuyen == QuanID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<HoNgheo>();
        }
        public HoNgheo HN_GetHoNgheoByID(string pHn)
        {

            var q = from p in db.HoNgheos
                    where p.Ma_HoNgheo == pHn && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Hn_NewHoNgheo(HoNgheo pHn)
        {
            db.HoNgheos.InsertOnSubmit(pHn);
            db.SubmitChanges();
        }

        public void Hn_EditHoNgheo(HoNgheo pHn)
        {
            var q = from p in db.HoNgheos
                    where p.Ma_HoNgheo == pHn.Ma_HoNgheo &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                HoNgheo obj = q.FirstOrDefault();
                
                obj.PhuongXa = db.PhuongXas.Single(c => c.Ma_Phuong == pHn.Ma_Phuong);
                obj.SoNha = pHn.SoNha;
                obj.ghiChu = pHn.ghiChu;
                obj.IsDeleted = pHn.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Hn_DelOrResHoNgheo(string pHn)
        {

            var q = from p in db.HoNgheos
                    where p.Ma_HoNgheo == pHn &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                HoNgheo obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region LoaiChiTieu
        public List<LoaiChiTieu> LCT_GetAllLoaiChiTieu()
        {
            var q = from p in db.LoaiChiTieus
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<LoaiChiTieu>();
        }

        public LoaiChiTieu LCT_GetLoaiChiTieuByID(int CtID)
        {

            var q = from p in db.LoaiChiTieus
                    where p.Ma_ChiTieu == CtID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region LoaiHinhKinhTe
        public List<LoaiHinhKinhTe> LHKT_GetAllLoaiHinhKinhTe()
        {
            var q = from p in db.LoaiHinhKinhTes
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<LoaiHinhKinhTe>();
        }

        public LoaiHinhKinhTe LHKT_GetLoaiHinhKinhTeByID(int LhktID)
        {

            var q = from p in db.LoaiHinhKinhTes
                    where p.Ma_LoaiHinhKinhTe == LhktID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region MucDieuDuong
        public List<MucDieuDuong> MDD_GetAllMucDieuDuong()
        {
            var q = from p in db.MucDieuDuongs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<MucDieuDuong>();
        }

        public MucDieuDuong MDD_GetMucDieuDuongByID(int MddID)
        {

            var q = from p in db.MucDieuDuongs
                    where p.Ma_MucDieuDuong == MddID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region NguoiCoCong

        public List<NguoiCoCong> NCC_GetAllNguoiCoCong()
        {
            var q = from p in db.NguoiCoCongs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<NguoiCoCong>();
        }

        public List<NguoiCoCong> NCC_GetNguoiCoCongByPhuongID(int PhuongID)
        {
            var q = from p in db.NguoiCoCongs
                    join j in db.PhuongXas on p.Ma_Phuong equals j.Ma_Phuong
                    where p.IsDeleted == false && p.Ma_Phuong == PhuongID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<NguoiCoCong>();
        }

        public List<NguoiCoCong> NCC_GetNguoiCoCongByQuanID(int QuanID)
        {
            var q = from p in db.NguoiCoCongs
                    join j in db.PhuongXas on p.Ma_Phuong equals j.Ma_Phuong
                    where p.IsDeleted == false && j.Ma_QuanHuyen == QuanID
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<NguoiCoCong>();
        }

        public NguoiCoCong NCC_GetNguoiCoCongByID(string pNcc)
        {

            var q = from p in db.NguoiCoCongs
                    where p.Ma_NguoiCoCong == pNcc && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Ncc_NewNguoiCoCong(NguoiCoCong pNcc)
        {
            db.NguoiCoCongs.InsertOnSubmit(pNcc);
            db.SubmitChanges();
        }

        public void Ncc_EditNguoiCoCong(NguoiCoCong pNcc)
        {
            var q = from p in db.NguoiCoCongs
                    where p.Ma_NguoiCoCong == pNcc.Ma_NguoiCoCong &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                NguoiCoCong obj = q.FirstOrDefault();
                obj.Ten_NguoiCoCong = pNcc.Ten_NguoiCoCong;
                obj.NamSinh = pNcc.NamSinh;
                obj.GioiTinh = pNcc.GioiTinh;
                obj.DiaChiChiTiet = pNcc.DiaChiChiTiet;
                obj.PhuongXa = db.PhuongXas.Single(c => c.Ma_Phuong == pNcc.Ma_Phuong);
                obj.DoiTuongCoCong =db.DoiTuongCoCongs.Single(c=>c.Ma_DoiTuongCoCong ==pNcc.Ma_DoiTuongCoCong);
                obj.IsDeleted = pNcc.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Ncc_DelOrResNguoiCoCong(string pNcc)
        {

            var q = from p in db.NguoiCoCongs
                    where p.Ma_NguoiCoCong == pNcc &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                NguoiCoCong obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region NguyenNhan_ThuNhapThap

        public List<NguyenNhan_ThuNhapThap> NNTNT_GetAllNguyenNhan_ThuNhapThap()
        {
            var q = from p in db.NguyenNhan_ThuNhapThaps
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<NguyenNhan_ThuNhapThap>();
        }

        public NguyenNhan_ThuNhapThap NNTNT_GetNguyenNhan_ThuNhapThapByID(int pNntnt)
        {

            var q = from p in db.NguyenNhan_ThuNhapThaps
                    where p.Ma_NguyenNhan == pNntnt && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }


        #endregion

        #region NguyenVongKhacPhuc

        public List<NguyenVongKhacPhuc> NVKP_GetAllNguyenVongKhacPhuc()
        {
            var q = from p in db.NguyenVongKhacPhucs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<NguyenVongKhacPhuc>();
        }

        public NguyenVongKhacPhuc NVKP_GetNguyenVongKhacPhucByID(int pNvkp)
        {

            var q = from p in db.NguyenVongKhacPhucs
                    where p.Ma_NguyenVong == pNvkp && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }


        #endregion

        #region PhuongXa

        public List<PhuongXa> PX_GetAllPhuongXa()
        {
            var q = from p in db.PhuongXas
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<PhuongXa>();
        }

        public PhuongXa PX_GetPhuongXaByID(int pPx)
        {

            var q = from p in db.PhuongXas
                    where p.Ma_Phuong == pPx && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public List<PhuongXa> PX_GetPhuongByIDQuan(int pIdQuan)
        {

            var q = from p in db.PhuongXas
                    where p.Ma_QuanHuyen == pIdQuan && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<PhuongXa>();
        }
        #endregion

        #region QuanHeGiaDinh

        public List<QuanHeGiaDinh> QHGD_GetAllQuanHeGiaDinh()
        {
            var q = from p in db.QuanHeGiaDinhs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<QuanHeGiaDinh>();
        }

        public List<QuanHeGiaDinh> QHGD_GetQuanHeGiaDinhByHoNgheoID(string idhongheo)
        {
            var q = from p in db.QuanHeGiaDinhs
                    where p.IsDeleted == false && p.Ma_HoNgheo==idhongheo
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<QuanHeGiaDinh>();
        }
        
        public QuanHeGiaDinh QHGD_GetQuanHeGiaDinhByIsChuHo_HoNgheoID(string idhongheo)
        {
            var q = from p in db.QuanHeGiaDinhs
                    where p.IsDeleted == false && p.Ma_HoNgheo == idhongheo && p.Ma_QuanHe ==1
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        
        public QuanHeGiaDinh QHGD_GetQuanHeGiaDinhByID(int pQhgd)
        {

            var q = from p in db.QuanHeGiaDinhs
                    where p.Ma_ChiTiet == pQhgd && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void Qhgd_NewQuanHeGiaDinh(QuanHeGiaDinh pQhgd)
        {
            db.QuanHeGiaDinhs.InsertOnSubmit(pQhgd);
            db.SubmitChanges();
        }

        public void Qhgd_EditQuanHeGiaDinh(QuanHeGiaDinh pQhgd)
        {
            var q = from p in db.QuanHeGiaDinhs
                    where p.Ma_ChiTiet == pQhgd.Ma_ChiTiet &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                QuanHeGiaDinh obj = q.FirstOrDefault();
                obj.Ma_HoNgheo = pQhgd.Ma_HoNgheo;
                obj.Ten_ThanhVien = pQhgd.Ten_ThanhVien;
                obj.GioiTinh = pQhgd.GioiTinh;
                obj.NamSinh = pQhgd.NamSinh;
                obj.QuanHeThanhVien = db.QuanHeThanhViens.Single(c => c.Ma_QuanHe == pQhgd.Ma_QuanHe);   
                obj.DanToc = db.DanTocs.Single(c => c.Ma_DanToc == pQhgd.Ma_DanToc);
                obj.IsDeleted = pQhgd.IsDeleted;
                try
                {
                    obj.ChinhSach = db.ChinhSaches.Single(c => c.Ma_ChinhSach == pQhgd.Ma_ChinhSach);
                }
                catch 
                {
                    obj.ChinhSach = null;
                }
                try
                {
                    obj.BaoTroXaHoi = db.BaoTroXaHois.Single(c => c.Ma_BaoTroXH == pQhgd.Ma_BaoTroXaHoi);
                }
                catch
                {
                    obj.BaoTroXaHoi = null;
                }
                try
                {
                    obj.CapHoc = db.CapHocs.Single(c => c.Ma_CapHoc == pQhgd.Ma_CapHoc);
                }
                catch
                {
                    obj.CapHoc = null;
                }
                try
                {
                    obj.TinhTrangViecLam = db.TinhTrangViecLams.Single(c => c.Ma_ViecLam == pQhgd.Ma_ViecLam);
                }
                catch
                {
                    obj.TinhTrangViecLam = null;
                }
                db.SubmitChanges();
            }
        }

        public void Qhgd_DelOrResQuanHeGiaDinh(int pQhgd)
        {

            var q = from p in db.QuanHeGiaDinhs
                    where p.Ma_ChiTiet == pQhgd &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                QuanHeGiaDinh obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region QuanHeThanhVien
        public List<QuanHeThanhVien> QHTV_GetAllQuanHeThanhVien()
        {
            var q = from p in db.QuanHeThanhViens
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<QuanHeThanhVien>();
        }

        public QuanHeThanhVien QHTV_GetQuanHeThanhVienByID(int QhtvID)
        {

            var q = from p in db.QuanHeThanhViens
                    where p.Ma_QuanHe == QhtvID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region QuanHuyen
        public List<QuanHuyen> QH_GetAllQuanHuyen()
        {
            var q = from p in db.QuanHuyens
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<QuanHuyen>();
        }

        public QuanHuyen QH_GetQuanHuyenByID(int QhID)
        {

            var q = from p in db.QuanHuyens
                    where p.Ma_QuanHuyen == QhID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region TaiSan
        public List<TaiSan> TS_GetAllTaiSan()
        {
            var q = from p in db.TaiSans
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<TaiSan>();
        }

        public TaiSan TS_GetTaiSanByID(int TsID)
        {

            var q = from p in db.TaiSans
                    where p.Ma_TaiSan == TsID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion

        #region ThanNhan_NguoiCoCong

        public List<ThanNhan_NguoiCoCong> TNNCC_GetAllTNNCC()
        {
            var q = from p in db.ThanNhan_NguoiCoCongs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ThanNhan_NguoiCoCong>();
        }

        public ThanNhan_NguoiCoCong TNNCC_GetThanNhan_NguoiCoCongByID(int pTnncc)
        {

            var q = from p in db.ThanNhan_NguoiCoCongs
                    where p.Ma_ThanNhan == pTnncc && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public List<ThanNhan_NguoiCoCong> TNNCC_GetThanNhan_NguoiCoCongByNguoiCoCongID(string IDNguoiCoCong)
        {
            var q = from p in db.ThanNhan_NguoiCoCongs
                    where p.IsDeleted == false && p.Ma_NguoiCoCong == IDNguoiCoCong
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<ThanNhan_NguoiCoCong>();
        }

        public void Tnncc_NewThanNhan_NguoiCoCong(ThanNhan_NguoiCoCong pTnncc)
        {
            db.ThanNhan_NguoiCoCongs.InsertOnSubmit(pTnncc);
            db.SubmitChanges();
        }

        public void Tnncc_EditThanNhan_NguoiCoCong(ThanNhan_NguoiCoCong pTnncc)
        {
            var q = from p in db.ThanNhan_NguoiCoCongs
                    where p.Ma_ThanNhan == pTnncc.Ma_ThanNhan &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ThanNhan_NguoiCoCong obj = q.FirstOrDefault();
                obj.Ma_NguoiCoCong = pTnncc.Ma_NguoiCoCong;
                obj.Ma_QuanHeThanhVien = pTnncc.Ma_QuanHeThanhVien;
                obj.Ten_ThanNhan = pTnncc.Ten_ThanNhan;
                obj.NamSinh = pTnncc.NamSinh;
                obj.GioiTinh = pTnncc.GioiTinh;
                obj.DiaChi_ChiTiet = pTnncc.DiaChi_ChiTiet;
                obj.Ma_Phuong = pTnncc.Ma_Phuong;
                obj.IsDeleted = pTnncc.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void Tnncc_DelOrResThanNhan_NguoiCoCong(int pTnncc)
        {

            var q = from p in db.ThanNhan_NguoiCoCongs
                    where p.Ma_ThanNhan == pTnncc &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                ThanNhan_NguoiCoCong obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region TinhTrangViecLam
        public List<TinhTrangViecLam> TTVL_GetAllTinhTrangViecLam()
        {
            var q = from p in db.TinhTrangViecLams
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<TinhTrangViecLam>();
        }

        public TinhTrangViecLam TTVL_GetTinhTrangViecLamByID(int TtvlID)
        {

            var q = from p in db.TinhTrangViecLams
                    where p.Ma_ViecLam == TtvlID && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }
        #endregion
        //

        #region USER

        public List<USER> U_GetAllUSER()
        {
            var q = from p in db.USERs
                    where p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.ToList();
            else
                return new List<USER>();
        }

        public USER U_GetUSERByID(int pU)
        {

            var q = from p in db.USERs
                    where p.UserID == pU && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public USER U_GetUSERByUserName(string pUName)
        {

            var q = from p in db.USERs
                    where p.UserName == pUName && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public USER U_GetUSERByUserNameAndPass(string pUName, string pPassWord)
        {

            var q = from p in db.USERs
                    where p.UserName == pUName && p.Password==pPassWord && p.IsDeleted == false
                    select p;
            if (q.Count() > 0)
                return q.FirstOrDefault();
            else
                return null;
        }

        public void U_NewUSER(USER pU)
        {
            db.USERs.InsertOnSubmit(pU);
            db.SubmitChanges();
        }

        public void U_EditUSER(USER pU)
        {
            var q = from p in db.USERs
                    where p.UserID == pU.UserID &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                USER obj = q.FirstOrDefault();
                obj.UserName = pU.UserName;
                obj.Password = pU.Password;
                obj.Role = pU.Role;
                obj.FullName = pU.FullName;
                obj.IsDeleted = pU.IsDeleted;
                db.SubmitChanges();
            }
        }

        public void U_DelOrResUser(int pU)
        {

            var q = from p in db.USERs
                    where p.UserID == pU &&
                    p.IsDeleted == false
                    select p;

            if (q.Count() > 0)
            {
                USER obj = q.FirstOrDefault();
                obj.IsDeleted = true;
                db.SubmitChanges();
            }
        }
        #endregion

        #region Dispose

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this._disposed)
            {
                if (disposing)
                {
                    if (db != null)
                    {
                        db.Dispose();
                    }
                }
                _disposed = true;
            }
        }

        protected bool Disposed
        {
            get
            {
                return _disposed;
            }
            set
            {
                _disposed = value;
            }
        }
        #endregion

        protected LuanVanDBDataContext DbDataContext
        {
            get
            {
                return db;
            }
        }
    }
}
